﻿/*
 * DataExport.cs
 * 
 * Copyright (C) 2011 Elena Shishkina
 * 
 * This file is part of ServerInventory.
 * 
 * ServerInventory is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * ServerInventory is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with ServerInventory.  If not, see <http://www.gnu.org/licenses/>.
 * 
 */
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ServerInventory
{
    class DataExport : Data
    {
        public void ExportDataCenters()
        {
            using (var conn = DbConnect())
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select * from dcs";
                    using (var rd = cmd.ExecuteReader())
                    {
                        writer("Id;Name;Address;Power;Notes\r\n");
                        while (rd.Read())
                        {
                            writer(String.Format("{0};{1};{2};{3};{4}\r\n", rd["id"], rd["name"], rd["address"], rd["power"], rd["notes"]));
                        }
                    }
                }
            }
        }

        public override void Process()
        {
            var table = param("table");
            switch (table)
            {
                case "dc": ExportDataCenters(); break;
                case "rack": ExportRacks(); break;
                case "server": ExportServers(); break;
                case "ip": ExportIPs(); break;
            }
        }

        private void ExportIPs()
        {
            using (var conn = DbConnect())
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"select
i.ip,
coalesce(s.name, v.name, s.model) as cname,
s.name as name,
v.name as vendor,
s.model,
r.name as rack,
d.name as dc
from server_ips i
left join inventory s on i.server_id = s.id
left join vendors v on s.vendor_id = v.id
left join racks r on s.rack_id = r.id
left join dcs d on r.dc_id = d.id
order by 
		 CAST(PARSENAME(ip, 4) AS INT),
         CAST(PARSENAME(ip, 3) AS INT),
         CAST(PARSENAME(ip, 2) AS INT),
         CAST(PARSENAME(ip, 1) AS INT)";
                    using (var rd = cmd.ExecuteReader())
                    {
                        writer("Ip;ServerName;Vendor;Model;Rack;Dc\r\n");
                        while (rd.Read())
                        {
                            writer(String.Format("{0};{1};{2};{3};{4};{5}\r\n", rd["ip"], rd["name"], rd["vendor"], rd["model"], rd["rack"], rd["dc"]));
                        }
                    }
                }
            }
        }

        private void ExportServers()
        {
            using (var conn = DbConnect())
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"select d.id as dc_id, d.name as dc, r.name as rack, i.*, v.name as vendor, v.color as vcolor, srv_ips.ips as srvip 
from inventory i 
left join racks r on r.id = i.rack_id
left join dcs d on d.id = r.dc_id
left join vendors v on i.vendor_id = v.id
left join
(select distinct server_id,
(stuff((select ',' + ip from server_ips i2 where i1.server_id = i2.server_id order by ip
for xml path(''), type, root).value('root[1]','nvarchar(max)'),1,1,'')) as ips
from server_ips i1) as srv_ips
on srv_ips.server_id = i.id
order by dc, rack, i.name";
                    using (var rd = cmd.ExecuteReader())
                    {
                        writer("Id;DcId;Dc;RackId;Rack;VendorId;Vendor;Name;Model;Type;Sn;In;Pusage;Pstate;Units;IPs;Mgmtip;Notes;Color;VendorColor\r\n");
                        while (rd.Read())
                        {
                            writer(String.Format("{0};{1};{2};{3};{4};{5};{6};{7};{8};{9};{10};{11};{12};{13};{14};{15};{16};{17};{18};{19}\r\n",
                                rd["id"], rd["dc_id"], rd["dc"], rd["rack_id"], rd["rack"], rd["vendor_id"], rd["vendor"], rd["name"], rd["model"], rd["type"], rd["sn"], rd["in"], rd["pusage"], rd["pstate"], rd["units"], rd["srvip"], rd["mgmtip"], rd["notes"], rd["color"], rd["vcolor"]));
                        }
                    }
                }
            }
        }

        private void ExportRacks()
        {
            using (var conn = DbConnect())
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"select r.*, d.name as dc, v.name as vendor
from dcs d
inner join racks r on d.id = r.dc_id
left join vendors v on v.id = r.vendor_id
order by dc, r.name";
                    using (var rd = cmd.ExecuteReader())
                    {
                        writer("Id;DcId;Dc;VendorId;Vendor;Name;Units;Notes\r\n");
                        while (rd.Read())
                        {
                            writer(String.Format("{0};{1};{2};{3};{4};{5};{6};{7}\r\n",
                                rd["id"], rd["dc_id"], rd["dc"], rd["vendor_id"], rd["vendor"], rd["name"], rd["units"], rd["notes"]
                                ));
                        }
                    }
                }
            }
        }

        public override void Headers(SetHeaderDelegate callback)
        {
            callback("Content-type", "application/csv");
            callback("Content-Disposition", "attachment;filename=" + param("table") + ".csv");
        }
    }
}
